package org.infodavid.common.test.jdbc;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * The Class MetadataHelper.
 */
public class MetadataHelper {

	/**
	 * The Class Index.
	 */
	public static class Index {

		/** The name. */
		private String name;

		/** The column. */
		private String column;

		/** The is unique. */
		private boolean isUnique;

		/** The table. */
		private final Table table;

		/**
		 * The Constructor.
		 * @param parent the parent
		 */
		public Index(final Table parent) {
			super();

			table = parent;
		}

		/**
		 * The Constructor.
		 * @param resultSet the result set
		 * @param meta the meta data
		 * @param parent the parent
		 * @throws Exception the exception
		 */
		public Index(final Table parent, final DatabaseMetaData meta, final ResultSet resultSet)
		throws Exception {
			super();

			table = parent;
			column = resultSet.getString("COLUMN_NAME");
			name = resultSet.getString("INDEX_NAME");
			isUnique = Boolean.parseBoolean(resultSet.getString("NON_UNIQUE"));
		}

		/**
		 * Gets the name.
		 * @return the name
		 */
		public String getName() {
			return name;
		}

		/**
		 * Checks if is unique.
		 * @return true, if checks if is unique
		 */
		public boolean isUnique() {
			return isUnique;
		}

		/**
		 * Gets the column.
		 * @return the column
		 */
		public String getColumnName() {
			return column;
		}

		/*
		 * (non-Javadoc)
		 * @see java.lang.Object#toString()
		 */
		@Override
		public String toString() {
			final StringBuffer buffer = new StringBuffer();

			buffer.append("INDEX = ");
			buffer.append(getName());
			buffer.append(", COLUMN = ");
			buffer.append(getColumnName());
			buffer.append(", UNIQUE = ");
			buffer.append(isUnique());
			buffer.append('\n');

			return buffer.toString();
		}

		/**
		 * Gets the parent table.
		 * @return the parent table
		 */
		public Table getTable() {
			return table;
		}
	}

	/**
	 * The Class Table.
	 */
	public static class Table {

		/** The columns. */
		private final Map<String,Column> columns = new HashMap<String,Column>();

		/** The name. */
		private final String name;

		/** The keys. */
		private final List<String> keys = new LinkedList<String>();

		/** The indexes. */
		private final List<Index> indexes = new LinkedList<Index>();

		/**
		 * The Constructor.
		 * @param conn the connection
		 * @param meta the meta data
		 * @param table the table
		 * @throws Exception the exception
		 */
		public Table(final Connection conn, final DatabaseMetaData meta, final String table)
		throws Exception {
			this(table);

			initialize(conn, meta);
		}

		/**
		 * The Constructor.
		 * @param table the table
		 */
		public Table(final String table) {
			super();

			name = table;
		}

		/**
		 * Initialize.
		 * @param conn the connection
		 * @param meta the meta
		 * @throws Exception the exception
		 */
		protected void initialize(final Connection conn, final DatabaseMetaData meta) throws Exception {
			ResultSet resultSet = meta.getColumns(null, "%", name, "%");
			Column column;
			Index index;

			while (resultSet.next()) {
				column = new Column(this, meta, resultSet);
				columns.put(column.getName(), column);
			}

			resultSet.close();
			resultSet = meta.getPrimaryKeys(null, null, name);
			keys.clear();

			while (resultSet.next()) {
				keys.add(resultSet.getString("COLUMN_NAME"));
			}

			resultSet.close();
			resultSet = meta.getIndexInfo(null, null, name, true, true);
			indexes.clear();

			while (resultSet.next()) {
				index = new Index(this, meta, resultSet);
				indexes.add(index);
			}
		}

		/**
		 * Gets the columns.
		 * @return the columns
		 */
		public Map<String,Column> getColumns() {
			return columns;
		}

		/**
		 * Gets the keys.
		 * @return the keys
		 */
		public List<String> getKeys() {
			return keys;
		}

		/**
		 * Gets the indexes.
		 * @return the indexes
		 */
		public List<Index> getIndexes() {
			return indexes;
		}

		/**
		 * Gets the name.
		 * @return the name
		 */
		public String getName() {
			return name;
		}

		/*
		 * (non-Javadoc)
		 * @see java.lang.Object#toString()
		 */
		@Override
		public String toString() {
			final StringBuffer buffer = new StringBuffer();

			buffer.append("TABLE_NAME = ");
			buffer.append(getName());
			buffer.append("\n KEYS = {");

			for (final String key : keys) {
				buffer.append(key);
				buffer.append(',');
			}

			buffer.append("}\n INDEXES = {");

			for (final Index index : indexes) {
				buffer.append('{');
				buffer.append(index);
				buffer.append('}');
				buffer.append(',');
			}

			buffer.append("}\n");

			for (final Column column : getColumns().values()) {
				buffer.append(column);
			}

			return buffer.toString();
		}
	}

	/**
	 * The Class Schema.
	 */
	public static class Schema {

		/** The tables. */
		private final Map<String,Table> tables = new HashMap<String,Table>();

		/** The name. */
		private final String name;

		/** The db product name. */
		private String dbProductName = null;

		/** The db product version. */
		private String dbProductVersion = null;

		/**
		 * The Constructor.
		 * @param conn the connection
		 * @param schema the schema
		 * @param table the table
		 * @throws Exception the exception
		 */
		public Schema(final Connection conn, final String schema, final String table) throws Exception {
			this(table);

			initialize(conn, schema);
		}

		/**
		 * The Constructor.
		 * @param table the table
		 */
		public Schema(final String table) {
			super();

			name = table;
		}

		/**
		 * Initialize.
		 * @param conn the connection
		 * @param schema the schema
		 * @throws Exception the exception
		 */
		protected void initialize(final Connection conn, final String schema) throws Exception {
			final String[] types = {
					"TABLE"
			};
			final DatabaseMetaData meta = conn.getMetaData();
			final ResultSet resultSet = meta.getTables(null, schema, "%", types);
			String value;

			dbProductName = meta.getDatabaseProductName();
			dbProductVersion = meta.getDatabaseProductVersion();

			while (resultSet.next()) {
				value = resultSet.getString("TABLE_NAME");
				tables.put(value, new Table(value));
			}

			resultSet.close();

			for (final Table table : tables.values()) {
				table.initialize(conn, meta);
			}
		}

		/**
		 * Gets the tables.
		 * @return the tables
		 */
		public Map<String,Table> getTables() {
			return tables;
		}

		/**
		 * Gets the name.
		 * @return the name
		 */
		public String getName() {
			return name;
		}

		/**
		 * Gets the db name.
		 * @return the db name
		 */
		public Object getDbName() {
			return dbProductName;
		}

		/**
		 * Gets the db version.
		 * @return the db version
		 */
		public Object getDbVersion() {
			return dbProductVersion;
		}

		/*
		 * (non-Javadoc)
		 * @see java.lang.Object#toString()
		 */
		@Override
		public String toString() {
			final StringBuffer buffer = new StringBuffer();

			buffer.append("SCHEMA_NAME = ");
			buffer.append(getName());
			buffer.append(", DB_NAME = ");
			buffer.append(getDbName());
			buffer.append(", DB_VERSION = ");
			buffer.append(getDbVersion());
			buffer.append('\n');

			for (final Table table : getTables().values()) {
				buffer.append(table);
			}

			return buffer.toString();
		}
	}

	/**
	 * The Class Column.
	 */
	public static class Column {

		/** The name. */
		private String name;

		/** The key or not. */
		private boolean isKey;

		/** The comment. */
		private String comment;

		/** The type. */
		private String type;

		/** The is nullable. */
		private boolean isNullable;

		/** The size. */
		private int size;

		/** The position. */
		private int position;

		/** The table. */
		private final Table table;

		/**
		 * The Constructor.
		 * @param parent the parent
		 */
		public Column(final Table parent) {
			super();

			table = parent;
		}

		/**
		 * The Constructor.
		 * @param resultSet the result set
		 * @param meta the meta data
		 * @param parent the parent
		 * @throws Exception the exception
		 */
		public Column(final Table parent, final DatabaseMetaData meta, final ResultSet resultSet)
		throws Exception {
			super();

			table = parent;
			name = resultSet.getString("COLUMN_NAME");
			type = resultSet.getString("TYPE_NAME");
			size = resultSet.getInt("COLUMN_SIZE");
			position = resultSet.getInt("ORDINAL_POSITION");
			isNullable = "YES".equalsIgnoreCase(resultSet.getString("IS_NULLABLE"));
			comment = resultSet.getString("REMARKS");

			final ResultSet columns = meta.getPrimaryKeys(null, null, table.getName());
			isKey = false;

			while (columns.next() && !isKey) {
				isKey = columns.getString("COLUMN_NAME").equalsIgnoreCase(name);
			}
		}

		/**
		 * Gets the name.
		 * @return the name
		 */
		public String getName() {
			return name;
		}

		/**
		 * Gets the primary key.
		 * @return the primary key
		 */
		public boolean isKey() {
			return isKey;
		}

		/**
		 * Gets the type.
		 * @return the type
		 */
		public String getType() {
			return type;
		}

		/**
		 * Gets the size.
		 * @return the size
		 */
		public int getSize() {
			return size;
		}

		/**
		 * Gets the comment.
		 * @return the comment
		 */
		public String getComment() {
			return comment;
		}

		/**
		 * Checks if is nullable.
		 * @return the isNullable
		 */
		public boolean isNullable() {
			return isNullable;
		}

		/**
		 * Gets the position.
		 * @return the position
		 */
		public int getPosition() {
			return position;
		}

		/*
		 * (non-Javadoc)
		 * @see java.lang.Object#toString()
		 */
		@Override
		public String toString() {
			final StringBuffer buffer = new StringBuffer();

			buffer.append("COLUMN = ");
			buffer.append(getName());
			buffer.append(", TYPE = ");
			buffer.append(getType());
			buffer.append(", KEY = ");
			buffer.append(isKey());
			buffer.append(", SIZE = ");
			buffer.append(getSize());
			buffer.append(", POSITION = ");
			buffer.append(getPosition());
			buffer.append(", NULLABLE = ");
			buffer.append(isNullable());
			buffer.append(", COMMENT = ");
			buffer.append(getComment());
			buffer.append('\n');

			return buffer.toString();
		}
	}

	/** The schema. */
	private final Schema schema;

	/**
	 * The Constructor.
	 * @param conn the connection
	 * @param name the name
	 * @throws Exception the exception
	 */
	public MetadataHelper(final Connection conn, final String name) throws Exception {
		schema = new Schema(conn, name, "%");
	}

	/**
	 * Gets the schema.
	 * @return the schema
	 */
	public Schema getSchema() {
		return schema;
	}

	/*
	 * (non-Javadoc)
	 * @see java.lang.Object#toString()
	 */
	@Override
	public String toString() {
		return schema.toString();
	}
}
